﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;
using IHISCETeim.Library;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using System.Drawing;
using NPOI.SS.UserModel;
using IHISCETeim.Model;
using System.Reflection;
using System.IO;
using NPOI.SS.Util;

namespace IHISCETeim.Web.Export.MacModel
{
    public partial class BatchExportOUnsuitableProdList : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {               
                return "质管_不适合品处理票.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "sheet1";
            }
        }
        #region 查询条件

        #region 属性 (查询条件)

        /// <summary>
        /// 排序字段 值为 id
        /// </summary>
        protected override string orderField
        {
            get
            {
                return string.IsNullOrEmpty(base.orderField) ? "UnsuitabledID" : base.orderField;
            }
        }
        /// <summary>
        ///  排序方式 desc或 asc
        /// </summary>
        protected override string orderDirection
        {
            get
            {
                return string.IsNullOrEmpty(base.orderDirection) ? "DESC" : base.orderDirection;
            }
        }


        protected override string SQLSort
        {
            get
            {
                return (orderField + " " + orderDirection);
            }
        }


        protected string StampNo
        {
            get
            {
                return GetRequestValue<string>("StampNo", "");

            }
        }
        protected string ItemCode
        {
            get
            {
                return GetRequestValue<string>("ItemCode", "");

            }
        }
        protected string Worker
        {
            get
            {
                return GetRequestValue<string>("Worker", "");

            }
        }
        protected string SerialNumber
        {
            get
            {
                return GetRequestValue<string>("SerialNumber", "");

            }
        }

        protected string SupplerName
        {
            get
            {
                return GetRequestValue<string>("SupplerName", "");
            }
        }
        protected string DownlineDateFrom
        {
            get
            {
                return GetRequestValue<string>("DownlineDateFrom", "");

            }
        }
        protected string DownlineDateTo
        {
            get
            {
                if (string.IsNullOrEmpty(DownlineDateFrom))
                {
                    return "";
                }
                else
                {
                    string todate = GetRequestValue<string>("DownlineDateTo", "");
                    if (string.IsNullOrEmpty(todate))
                        return Convert.ToDateTime(DownlineDateFrom).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
                    else
                        return todate;
                }

            }
        }

        protected string ExportFileName
        {
            get
            {
                return GetRequestValue<string>("ExportFileName", "");
            }
        }

        protected string commfieldsname
        {
            get
            {
                return GetRequestValue<string>("commfieldsname", "");
            }
        }
        protected string commfieldsvalue
        {
            get
            {
                return GetRequestValue<string>("commfieldsvalue", "");
            }
        }
        #endregion
        protected string GetWhereSql()
        {
            string wheresql = " 1=1 ";
            if (!string.IsNullOrEmpty(ItemCode))
            {
                wheresql = string.Format(" (itemcode like '%{0}%' or itemname like '%{0}%') ", ItemCode);
            }

            if (!string.IsNullOrEmpty(SerialNumber))
            {
                if (!string.IsNullOrEmpty(wheresql))
                    wheresql += string.Format(" and SerialNumber like '%{0}%'", SerialNumber);
                else
                    wheresql = string.Format(" SerialNumber like '%{0}%'", SerialNumber);
            }

            if (!string.IsNullOrEmpty(StampNo))
            {
                if (!string.IsNullOrEmpty(wheresql))
                    wheresql += string.Format(" and StampNo like'%{0}%'", StampNo);
                else
                    wheresql = string.Format(" StampNo l ike'%{0}%'", StampNo);
            }

            if (!string.IsNullOrEmpty(SupplerName))
            {
                if (!string.IsNullOrEmpty(wheresql))
                    wheresql += string.Format(" and SupplerName like '%{0}%'", SupplerName);
                else
                    wheresql = string.Format(" SupplerName like '%{0}%'", SupplerName);
            }

            string DOWNSQL = "";
            if (!string.IsNullOrEmpty(DownlineDateFrom))
            {
                DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("IssueDate", QueryOperatorsEnum.范围, DownlineDateFrom + "|" + DownlineDateTo);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }

            if (!string.IsNullOrEmpty(ExportFileName))
            {
                if (!string.IsNullOrEmpty(wheresql))
                    wheresql += string.Format(" and ExportFileName like'%{0}%'", ExportFileName);
                else
                    wheresql = string.Format(" ExportFileName like'%{0}%'", ExportFileName);
            }

            if (!string.IsNullOrEmpty(commfieldsname) && !string.IsNullOrEmpty(commfieldsvalue))
            {
                if (!string.IsNullOrEmpty(wheresql))
                    wheresql += string.Format(" and {0} like'%{1}%'", commfieldsname, commfieldsvalue);
                else
                    wheresql = string.Format(" {0} like'%{1}%'", commfieldsname, commfieldsvalue);
            }

            if (!string.IsNullOrEmpty(Worker))
            {
                if (!string.IsNullOrEmpty(wheresql))
                    wheresql += string.Format(" and Worker like'%{0}%'", Worker);
                else
                    wheresql = string.Format(" Worker like'%{0}%'", Worker);
            }


            return wheresql;
        }

        #endregion

        #region 用于记录模板对应的单元格名称

        #endregion

        protected void Page_Load(object sender, EventArgs e)
        {
            InitializeWorkbook();
            //所有需要导出的ID
            DataTable dtids = OUnsuitableProdController.GetBatchExportId(GetWhereSql());
            int SheetIndex = 1; 
            foreach (DataRow dr in dtids.Rows)
            {
                SingExportProc(Convert.ToInt32(dr["UnsuitabledID"]), SheetIndex);
                SheetIndex++;
            }
            hssfworkbook.RemoveSheetAt(0);
            WriteToFile();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="UnsuitabledID"></param>
        /// <param name="SheetIndex">从1开始</param>
        protected void SingExportProc(int UnsuitabledID,int SheetIndex)
        {
           
            OUnsuitableProdInfo ObjProd = OUnsuitableProdController.GetById(UnsuitabledID);
            try
            {
                ISheet sheet1 = hssfworkbook.CloneSheet(0);
                hssfworkbook.SetSheetName(SheetIndex, ObjProd.StampNo);

                Type typeUnsu = ObjProd.GetType();
                PropertyInfo[] propArry = typeUnsu.GetProperties();

                int rowIndex = 0;
                int cellIndex = 0;
                string trueWriteTag = "";
                object provalue = null;
                string PropertyTypeString = "System.String";
                foreach (var item in propArry)
                {
                    provalue = item.GetValue(ObjProd, null);//属性值
                    PropertyTypeString = item.PropertyType.ToString();
                    //获取特性配置值
                    object[] objRowIndex = item.GetCustomAttributes(typeof(ExcelRowIndexAttribute), false);
                    if (objRowIndex.Length > 0)//说明该属性使用了ExcelRowIndexAttribute标记
                    {
                        rowIndex = (objRowIndex[0] as ExcelRowIndexAttribute).RowIndex;

                    }
                    else
                    {
                        continue;//没有行特性,进入下一个
                    }
                    object[] objCellIndex = item.GetCustomAttributes(typeof(ExcelCellIndexAttribute), false);
                    if (objCellIndex.Length > 0)//说明该属性使用了ExcelRowIndexAttribute标记
                    {
                        cellIndex = (objCellIndex[0] as ExcelCellIndexAttribute).CellIndex;
                    }

                    object[] objBoolCell = item.GetCustomAttributes(typeof(ExcelBoolCellAttribute), false);
                    if (objBoolCell.Length > 0)//说明该属性使用了ExcelRowIndexAttribute标记
                    {
                        trueWriteTag = (objBoolCell[0] as ExcelBoolCellAttribute).TrueWriteTag;
                        if (Convert.ToBoolean(provalue))
                        {
                            provalue = trueWriteTag;
                        }
                        else
                        {
                            provalue = "";
                        }
                        PropertyTypeString = "System.String";
                    }


                    ConvertDateTypeHelper.WriteExcelDateType(sheet1.GetRow(rowIndex).GetCell(cellIndex), PropertyTypeString, provalue);
                    ////写入值
                    //if (provalue == null)
                    //    provalue = "";
                    // sheet1.GetRow(rowIndex).GetCell(cellIndex).SetCellValue(provalue.ToString());

                }
                //2.写入图片
                InsertImages(sheet1, UnsuitabledID);
                //3.写入返回入报废相关列表
                InsertDetail(sheet1, UnsuitabledID);

                sheet1.ForceFormulaRecalculation = true;
            }
            catch (Exception EXC)
            {
                Response.Write(EXC.Message + "<br/>" + ObjProd.StampNo);
                throw new Exception(EXC.Message);
               
            }

        }

        #region 加载图片方法
        public void InsertImages(ISheet sheet1, int UnsuitabledID)
        {
            List<OUnsuitableProdImgInfo> listImg = OUnsuitableProdController.OUnsuitableProdImg_GetAllByUnsuitabledID(UnsuitabledID);
            int listImgCount = listImg.Count();
            if (listImgCount > 0)
            {
                int BaseImgRow = 10;
                int BaseImgCell = 17;
                int ImgRowAddRow = 6;
                int ImgCellAddCell = 9;
                int imgRowIndex = 0;//计数用
                int imgCellIndex = 0;

                int showRowindex = 0;
                int showCellindex = 0;
                int tempRowAdd = 0;
                int tempCellAdd = 0;
                int tempLasCellAddVar = 0;
                switch (listImgCount)
                {
                    case 1:
                        ImgRowAddRow = 16;
                        ImgCellAddCell = 16;
                        tempLasCellAddVar = 1;
                        break;
                    case 2:
                        ImgRowAddRow = 7;
                        ImgCellAddCell = 16;
                        tempRowAdd = 0;
                        tempLasCellAddVar = 1;
                        break;
                    case 3:
                    case 4:
                        ImgRowAddRow = 7;
                        ImgCellAddCell = 8;
                        tempRowAdd = 0;
                        tempLasCellAddVar = 0;
                        break;
                    case 5:
                    case 6:
                        ImgRowAddRow = 5;
                        ImgCellAddCell = 8;
                        tempRowAdd = 0;
                        tempLasCellAddVar = 0;
                        break;
                    default:
                        break;
                }


                foreach (OUnsuitableProdImgInfo objItem in listImg)
                {

                    showRowindex = BaseImgRow + (imgRowIndex / 2) * ImgRowAddRow + tempRowAdd;
                    showCellindex = BaseImgCell + (imgCellIndex / 1) * ImgCellAddCell + tempCellAdd;
                    //加图
                    HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
                    HSSFClientAnchor anchor;
                    anchor = new HSSFClientAnchor(0, 0, 0, 0, showCellindex, showRowindex, showCellindex + ImgCellAddCell + tempLasCellAddVar, showRowindex + ImgRowAddRow);
                    anchor.AnchorType = 3;

                   
                    try
                    {//加载图片，如果出错，则不管
                        HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(objItem.IMGAbsolutePath, hssfworkbook));
                        picture.LineStyle = LineStyle.DashSys;
                        picture.LineWidth = 12700;
                        picture.SetLineStyleColor(0, 0, 0);
                    }
                    catch
                    {

                    }
                    //图片说明
                    switch (listImgCount)
                    {
                        case 1:
                            sheet1.AddMergedRegion(new CellRangeAddress(showRowindex + ImgRowAddRow, showRowindex + ImgRowAddRow + 1, showCellindex, showCellindex + ImgCellAddCell));
                            sheet1.GetRow(showRowindex + ImgRowAddRow).GetCell(showCellindex).SetCellValue(objItem.Title.ToString());
                            break;
                        case 2:
                            sheet1.AddMergedRegion(new CellRangeAddress(showRowindex + ImgRowAddRow, showRowindex + ImgRowAddRow + 1, showCellindex, showCellindex + ImgCellAddCell));
                            sheet1.GetRow(showRowindex + ImgRowAddRow).GetCell(showCellindex).SetCellValue(objItem.Title.ToString());

                            imgRowIndex = 2;
                            tempRowAdd = 2;
                            imgCellIndex = 0;
                            break;
                        case 3:
                        case 4:
                            sheet1.AddMergedRegion(new CellRangeAddress(showRowindex + ImgRowAddRow, showRowindex + ImgRowAddRow + 1, showCellindex, showCellindex + ImgCellAddCell));
                            sheet1.GetRow(showRowindex + ImgRowAddRow).GetCell(showCellindex).SetCellValue(objItem.Title.ToString());

                            imgRowIndex++;
                            imgCellIndex = imgCellIndex == 0 ? 1 : 0;

                            tempRowAdd = imgRowIndex >= 2 ? 2 : 0;
                            tempCellAdd = imgCellIndex;
                            break;
                        case 5:
                        case 6:
                            sheet1.AddMergedRegion(new CellRangeAddress(showRowindex + ImgRowAddRow, showRowindex + ImgRowAddRow, showCellindex, showCellindex + ImgCellAddCell));
                            sheet1.GetRow(showRowindex + ImgRowAddRow).GetCell(showCellindex).SetCellValue(objItem.Title.ToString());

                            imgRowIndex++;
                            imgCellIndex = imgCellIndex == 0 ? 1 : 0;

                            tempRowAdd = imgRowIndex >= 2 ? (imgRowIndex / 2) : 0;
                            // tempRowAdd = ((imgRowIndex == 2) || (imgRowIndex==4)) ? 2 : 0;
                            tempCellAdd = imgCellIndex;
                            break;
                        default:

                            break;
                    }
                    //设置对齐方式
                    ICellStyle styleMiddle = hssfworkbook.CreateCellStyle();
                    styleMiddle.WrapText = true;
                    styleMiddle.Alignment = HorizontalAlignment.LEFT;
                    styleMiddle.VerticalAlignment = VerticalAlignment.CENTER;
                    IFont font = hssfworkbook.CreateFont();
                    font.FontName = "宋体";
                    font.FontHeightInPoints = 10;
                    styleMiddle.SetFont(font);
                    //设置边框
                    styleMiddle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
                    styleMiddle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
                    styleMiddle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
                    styleMiddle.BorderTop = NPOI.SS.UserModel.BorderStyle.DOTTED;
                    sheet1.GetRow(showRowindex + ImgRowAddRow).GetCell(showCellindex).CellStyle = styleMiddle;
                    // sheet1.AddMergedRegion(new CellRangeAddress(showRowindex + ImgRowAddRow+1, showRowindex + ImgRowAddRow+1, showCellindex, showCellindex + ImgCellAddCell));
                    // sheet1.GetRow(showRowindex + ImgRowAddRow+1).GetCell(showCellindex).SetCellValue(objItem.Title.ToString());

                    //
                    // imgRowIndex++;
                    // imgCellIndex = imgCellIndex == 0 ? 1 : 0;
                }

            }
        }
        public static int LoadImage(string path, HSSFWorkbook wb)
        {
            FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);
            byte[] buffer = new byte[file.Length];
            file.Read(buffer, 0, (int)file.Length);
            return wb.AddPicture(buffer, PictureType.JPEG);
        }

        #endregion

        #region 加载返回及报废件明细
        public void InsertDetail(ISheet sheet1, int UnsuitabledID)
        {
            List<OUnsuitableProdDetailInfo> listDetail = OUnsuitableProdController.OUnsuitableProdDetail_GetAllByUnsuitabledID(UnsuitabledID);
            //返回
            List<OUnsuitableProdDetailInfo> listReturnDetail = listDetail.Where(e => e.DetailType == "返回").ToList();
            int baseFHIndex = 38;
            foreach (OUnsuitableProdDetailInfo item in listReturnDetail)
            {
                sheet1.GetRow(baseFHIndex).ZeroHeight = false;
                sheet1.GetRow(baseFHIndex).GetCell(4).SetCellValue(item.DItemCode);
                sheet1.GetRow(baseFHIndex).GetCell(11).SetCellValue(item.DItemName);
                sheet1.GetRow(baseFHIndex).GetCell(18).SetCellValue(item.DQuantity.ToString("N0"));
                sheet1.GetRow(baseFHIndex).GetCell(25).SetCellValue(item.IsReturned ? "√" : "");
                sheet1.GetRow(baseFHIndex).GetCell(31).SetCellValue(item.IsScrap ? "√" : "");

                baseFHIndex++;
            }
            //报废
            List<OUnsuitableProdDetailInfo> listBFDetail = listDetail.Where(e => e.DetailType == "报废").ToList();
            int baseBFIndex = 51;
            foreach (OUnsuitableProdDetailInfo item in listBFDetail)
            {
                sheet1.GetRow(baseBFIndex).ZeroHeight = false;
                sheet1.GetRow(baseBFIndex).GetCell(4).SetCellValue(item.DItemCode);
                sheet1.GetRow(baseBFIndex).GetCell(11).SetCellValue(item.DItemName);
                sheet1.GetRow(baseBFIndex).GetCell(18).SetCellValue(item.DQuantity.ToString("N0"));
                sheet1.GetRow(baseBFIndex).GetCell(25).SetCellValue(item.IsDefectivePrd ? "√" : "");
                sheet1.GetRow(baseBFIndex).GetCell(31).SetCellValue(item.IsScrap ? "√" : "");

                baseBFIndex++;
            }
        }
        #endregion
        protected override string GetXlsFileName(string oldFileName)
        {
            //return base.GetXlsFileName(oldFileName);

            return string.Format("{0}_{1}.xls","批量导出不适合品处理票",DateTime.Now.ToString("yyyy-MM-dd")).Trim();
        }

    }
}